Welcome

Row

Project Status

Current focus area(s):

  • EDA, feature engineering, determine modeling goal(s) & method(s).
  • Determine if I want to use R or Python for remaining analysis.

Project Description

This project explores insights about hair salon appointments.

Row

Notes

  • Source code for this project can be found on my GitHub repo at Hair_Salon_No_Show in the index.Rmd file.

  • This website is best viewed with a large/maximized window. A larger monitor/screen (24" or larger) may be required.

Project Scenario

  • A small hair salon wants to understand its business and related data to better serve their clients.
  • Although they use a POS system, the owner/manager would like to look deeper than the receipts.
  • The salon manager and select staff members indicated that they typically use Excel to generate reports and/or review data. However, they feel that using Excel is becoming obsolete for their needs.
  • As an exploratory step before developing a longer-term strategy for their data and analytics needs, the business would like to see an example(s) of:
    • A better data management tool/platform.
    • Another type of dashboarding and/or reporting tool/platform.
    • Insights to be gained from those dashboards/reports.
    • Different analysis techniques employed and what insights those methods can provide.

Row

Software, Packages, Extensions & Languages

Software Used Packages Used (Helpful) VS Code Extensions Coding Languages Used
Mac OSx Catalina tidyverse Ascii Tree Generator R
R kableExtra Bracket Pair Colorizer 2 MySQL
RStudio RMySQL vscode-icons HTML
MySQL Community Server plotly
MySQL Workbench DT
Microsoft VS Code
Microsoft Excel


*Refer to README.md for the list of each software, package, and extension version used for this project.

Non-Scenario Project Goals

  • Use R/RStudio & MySQL, separately and as integrated tools, to perform analysis.
  • Set up a database schema as a simulated business requirement, which includes:
    • Raw data exploration
    • Database table design and relationship identification
    • Raw data extraction and upload into MySQL
  • Use flexdashboards in RStudio/RMarkdown as a workflow framework
  • Develop dashboard page(s)
  • Use HTML tags and other packages to experiement with RMarkdown HTML output file and Markdown document design features
  • Practice other modeling techniques

About

About Pre-DB Exploration

  • The information contained on each page under the Pre-DB Explore tab was used to gather information used for a MySQL database design and corresponding table creation.


  • This information helped:
    • identify what tables to create
    • determine how each table could/would link to one another
    • determine their respective primary & foreign keys

Client Cancellations

Row

data file

Client Cancellations

data dimensions

nrow ncol
243 7

Row

column classes/data types

class_type
Cancel Date character
Code character
Service character
Staff character
Booking Date character
Canceled By character
Days character

data sample

Cancel Date Code Service Staff Booking Date Canceled By Days
05/22/2018 MASA01 CFC JOANNE 05/24/2018 ADMIN 2
06/27/2018 MASA01 CBAL KELLY 06/27/2018 ADMIN 0
07/03/2018 MASA01 SHCM BECKY 07/10/2018 BECKY 7
07/10/2018 MASA01 SHCC JJ 07/11/2018 KELLY 1
07/24/2018 TRUA01 SHCM KELLY 07/24/2018 TANYA 0

Row

max column width (in characters)

max_num_char
Cancel Date 10
Code 6
Service 9
Staff 6
Booking Date 10
Canceled By 6
Days 3

missing/null values index number

Cancel Date Code Service Staff Booking Date Canceled By Days

Row

unique Code values

?A01 BLAR01 CARB01 DEAM01 FLAD01
.L01 BOES01 CARK01 DES01 FLAS01
ABL*01 BORJ01 CARS01 DEYS01 GAD*01
ALCV01 BREF01 CHAD01 DONB01 GOOA01
APTM01 BREH01 CHOC01 DOWR01 GOOA02
ARRM01 BROS01 COLB01 DUFD01 GOSD01
ATMM01 BROT01 COLG01 ELCB01 GUYD01
AURM01 BUDG02 COLS01 ERWS01 HEIM01
BHAB01 BUSM01 DACJ01 EZIR01 HELJ01
BISJ01 CAND01 DAJR01 FISV01 HENB01

unique Service values

CALC CHLFH CT SHCM
CBAL CHLHH CTU SHCW
CCAMO CHLPHC FRI SMARTBOND
CCO CHLPL SBD SMO
CDPB CMT SDUD TRE 1
CFC CON SHCC -place_holder-

unique Staff values

BECKY JOANNE SINEAD
JJ KELLY TANYA

unique Cancelled By values

ADMIN JOANNE TANYA
BECKY KELLY -place_holder-
JJ SINEAD -place_holder-

Future Bookings

Row

data file

Future Bookings

data dimensions

nrow ncol
1906 6

Row

column classes/data types

class_type
Code character
Staff character
Service character
Date character
Time character
TimeInt numeric

data sample

Code Staff Service Date Time TimeInt
JUNJ01 JJ CON 03/14/2018 3:50:00 PM 950
NORJ01 JJ SHCM 03/14/2018 4:00:00 PM 960
CORS01 JJ SHCW 03/15/2018 2:10:00 PM 850
KERT01 JJ SHCW 03/15/2018 10:00:00 AM 600
LEHJ01 JJ SHCM 03/15/2018 6:00:00 PM 1080

Row

max column width (in characters)

max_num_char
Code 6
Staff 6
Service 9
Date 10
Time 11
TimeInt 4

missing/null values index number

Code Staff Service Date Time TimeInt
163

Row

unique Staff values

BECKY
HOUSE
JJ
JOANNE
KELLY
SINEAD
TANYA

unique Service values

CAL CDPB CHLPHC FRI SHCM
CALC CFC CHLPL MISC SHCW
CAS CHLFH CMT SBD SMARTBOND
CBAL CHLFHC CON SBD5+1 SMO
CCAMO CHLHH CT SDUD TRE 1
CCO CHLHHC CTU SHCC -place_holder-

Row

unique Code values

?A01 BENS01 BYRP01 COXH01 ERWS01 GRIF01 IADA01 KOOM02 LIND01 MCGD01 NEEL01 PERB01 RUDJ01 SMAN01 TAYR01 WALT01
.L01 BENS02 CABL01 CRAY01 ESKR01 GRIP01 INFB01 KOSA01 LINJ01 MCGM01 NELT01 PHIC01 RUGN01 SMIC01 TAYT01 WANM01
ABL*01 BERH01 CALC01 CRIV01 EZIR01 GROJ01 INGS01 KOSI01 LINM01 MCGV01 NEML01 PHIJ01 RUMJ02 SMIL01 TERJ01 WARG01
ABLD01 BERI01 CALD01 CROB01 FAIL01 GROR01 IORC01 KOZC01 LINM02 MCKS01 NEMM01 PIJM01 RUPN01 SMIS01 THIA01 WARK01
ADAP01 BESJ01 CAND01 CULT01 FANM01 GUBD01 IRWE01 KRAS01 LINR01 MCKT01 NGC01 PIPJ01 RUTA01 SOLR01 THOA01 WARN01
AGOE01 BEZK01 CARB01 CURK01 FAUA01 GUYD01 JACA01 KRAV01 LOCS01 MCLE01 NGUJ01 PIVS01 RUTM01 SORM01 THOD01 WASS01
AINM01 BHAJ01 CARC01 CYRA01 FEDS01 HACL01 JAMM01 KREJ01 LOSC01 MCLI01 NGUS01 PLUJ01 RUTR01 SPEC01 THOJ01 WAX*01
AITS01 BHAT01 CARK01 CZES01 FELH01 HADD01 JARM01 KRIA01 LUCL01 MCMC01 NICL01 PN01 SAMB01 SPEK01 THOW01 WEEJ01
ALCV01 BIAB01 CASH01 DACJ01 FER*01 HAMA01 JARS01 KRIJ01 LUL01 MCND01 NICM01 POIJ01 SAMJ01 SPIJ01 THOW02 WEES01
ALIA01 BINP01 CASN01 DAJR01 FERK01 HAMD01 JASA01 KUCM02 LURM01 MCNR01 NICP01 POLB01 SARA01 SPRK01 TIEM01 WEIA01
ALIR01 BISD01 CAYK01 DALR01 FIGM01 HAMN01 JIAL01 KUKH01 LYK01 MCPL01 NICS01 POOD01 SARK01 SQUN01 TIML01 WEIS01
ALIR02 BISS01 CECA01 DAMK01 FIND01 HARW01 JM01 KUKK01 LYL01 MCWC01 NIDN01 POTR01 SARM01 STAD01 TINA01 WELL01
ALLM01 BIZK01 CEEJ01 DANG01 FINW01 HASS01 JOHD01 KULR01 LYNG01 MELE01 NIES02 POUG01 SASA01 STAT01 TINA02 WELN01
ALVJ01 BLAJ01 CHA*01 DANJ01 FIOJ01 HEIM01 JOHJ01 KUMA01 LYOH01 MER*01 NIKK01 PRIL01 SASM01 STEA01 TINT01 WHIA01
ALZR01 BLAR01 CHAE01 DANJ02 FISV01 HELJ01 JOHM01 KURG01 MACA01 MERM01 NIZS01 PRIM01 SAVC01 STEB01 TINT02 WICJ01
AMAB01 BOES01 CHAG01 DANJ03 FLAC01 HENA01 JONC01 KUZD01 MACB01 MERN01 NORB01 PUGS01 SAVP01 STEC01 TOBJ01 WIL*01
AMAK01 BONT01 CHEW01 DAS*01 FLAD01 HENB01 JONE01 KWOR01 MACD01 MESK01 NORE01 PURD01 SCHC01 STED01 TOHS01 WILB01
ANDE01 BOOR01 CHIC01 DAV*01 FLAS01 HENI01 JONK01 LAIA01 MACK01 MESN01 NORJ01 RABH01 SCHH01 STEH01 TOLM01 WILK01
ANDE03 BORJ01 CHIT01 DAVA01 FLEB01 HENK01 JONW01 LAMC01 MACM01 METJ01 NORL01 RADP01 SCHJ01 STEI01 TOMA01 WILS01
ANDJ01 BOSJ01 CHMY01 DAWP01 FLEC01 HERD01 JOYA01 LAMD01 MACR01 MICK01 NOTA01 RANZ01 SCHJ02 STEJ01 TOMV01 WILT01
ANDM01 BOTC01 CHOK01 DD01 FLEL01 HESM01 JUDJ01 LANI01 MACS01 MICK02 NOWT01 REDM01 SCHJ03 STEK01 TOPS01 WINJ01
ANDN01 BOTJ01 CHOT01 DEAM01 FOXS01 HIGA01 JUNJ01 LANR01 MACT01 MICM01 OBRK01 REIC01 SCHP01 STEK02 TRAJ01 WINL01
APPS01 BOTK01 CHRJ01 DEH01 FRAL01 HILD01 JURS01 LANS01 MACT02 MILE01 ODOA01 REIL01 SCOJ01 STEL01 TRAS01 WITC01
APTM01 BOTN01 CHUA01 DEK01 FRAM01 HILE01 KALA01 LAS01 MACV01 MILJ01 OGDJ01 REIM01 SCOL01 STEL02 TRAS02 WONM02
ARAI01 BOTS01 CHUC01 DELC01 FRAW01 HILJ01 KASJ01 LAWJ01 MAEJ01 MINA01 OGOE01 RETM01 SEDD01 STEM01 TRES01 WOOD01
ARBA01 BOWM01 CHUH01 DENB01 FREJ01 HIMB01 KATM01 LEAS01 MAF*01 MINA02 OGRS01 RICC01 SEEK01 STEM02 TRIL01 WOOD02
ARMG01 BOWV01 CHUJ01 DES01 FREK01 HIMJ01 KATS01 LEB01 MAIK01 MOGS01 OHA*01 RICP01 SENM01 STEP01 TRUA01 WOOJ01
ARRM01 BOYL01 CLAJ01 DESA01 FZUA01 HOAB01 KEAJ01 LEEJ01 MAKM01 MOHM01 OHAG01 RICS01 SHAA01 STOD01 TRUA02 WOOJ02
ASIS01 BRAN01 CLAM01 DEUD01 GA01 HOAS01 KEIS01 LEEK01 MAKP01 MOHR01 OHLJ01 RIDY01 SHAG01 STOK01 TSAR01 WRI*01
ATKN01 BREF01 CLEE01 DEYS01 GAGC01 HOC01 KELA01 LEEM01 MALJ01 MOIB01 OKEM01 RIHM01 SHAI01 STOK02 TSUL01 WRIM01
ATLB01 BREH01 CLUS01 DHAL01 GALB01 HOFA01 KELR01 LEEN01 MANL01 MOLA01 OSES01 RIJR01 SHAM01 STOS01 TUMM01 WYRC01
AVPR01 BREM01 COAA01 DHAN01 GALG01 HOFJ01 KEN*01 LEEO01 MAPJ01 MOLC01 OWED01 RIVA01 SHAP01 STOV01 TURA01 YAGL01
AYKS01 BRES01 COAJ01 DHAS01 GARJ01 HOG*01 KENH01 LEEP01 MARA01 MONE01 OZDC02 RIZB01 SHAS01 STRH01 TYRA01 YANC01
BAIL01 BRIK01 COHH01 DHAS02 GARJ02 HOLD01 KENJ01 LEES01 MARD01 MONL01 OZIS01 ROBM01 SHEK01 STUM01 TYRK01 YANJ01
BAIS01 BROA01 COLB01 DHOA01 GELK01 HOLG01 KERT01 LEEV01 MARG01 MOOE01 PAE01 ROBT01 SHIG01 SUNS01 VALT01 YANM01
BALJ01 BROF01 COLC01 DINJ01 GHOP01 HOLL01 KHAN01 LEFN01 MARK01 MOOH01 PAIK01 RODP01 SHIL01 SUPR01 VANJ01 YATT01
BALO01 BROS01 COLE01 DIRK01 GIAL01 HOLS01 KHUN01 LEGB01 MARM01 MOOJ01 PANB01 ROGE01 SIDA01 SWAM01 VARE01 YEHC01
BANR01 BROS02 COLG01 DODK01 GIEC01 HOP01 KIMF01 LEHF01 MARO01 MORB01 PANP01 ROLD01 SILG01 TAGS02 VARL01 YOUS01
BARA01 BROT01 COLJ01 DOLD01 GOOA02 HOS01 KIMH01 LEHJ01 MARR01 MORD01 PARC01 RONM01 SINJ01 TAMA01 VAUD01 YUES01
BARE01 BUDG02 COLJ02 DONN01 GORM01 HOT01 KIMS01 LEHN01 MART01 MOSR01 PARD01 ROPR01 SINM01 TANJ01 VELA01 ZHAV01
BARJ01 BUEM01 COLR01 DORR01 GOSD01 HOUC01 KIMY01 LEIF01 MASA01 MOYJ01 PARJ01 ROSD01 SINS01 TASJ01 VERD01 ZHIJ01
BARW01 BUFJ01 COLR02 DOWR01 GOUE01 HOUN01 KINN01 LEIG01 MASR01 MUIK01 PARJ02 ROSD02 SINS02 TASR01 VERM01 ZIMJ01
BATP01 BUFW01 COMJ01 DRAA01 GRAA01 HOWC01 KIRJ01 LEL01 MAUA01 MULN01 PARS01 ROSL01 SINT01 TAYB01 VERR01 ZUGL01
BEAA01 BURD01 CONJ01 DRAR01 GREB01 HUAR01 KIRR01 LELS01 MAXI01 MUMK01 PATB01 ROSM01 SINW01 TAYD01 WAGL01 ZUKS01
BEAA02 BURK01 CONK01 DSIC01 GREC01 HUDM01 KLEA01 LEM01 MCAJ01 MUNJ01 PAUB01 ROST01 SIRM01 TAYE01 WAGM01 NA
BEAL01 BURL01 COOM01 DUBO01 GREL01 HUDS01 KM01 LEVL01 MCCB01 MUNM01 PEDM01 ROUT01 SITB01 TAYJ01 WAGW01 -place_holder
BEBY01 BURT01 CORS01 DUFD01 GREM01 HUTP01 KOEE01 LEWH01 MCCK01 MURS01 PEDM02 ROWD01 SKUD01 TAYJ02 WAIS01 -place_holder
BELK01 BUSC01 CORS02 DUFM01 GREN01 HUYK01 KOLK01 LEWH02 MCGA01 MURS02 PEEL01 ROZM01 SKUT01 TAYL01 WAL*01 -place_holder
BENC01 BUSM01 COWL01 ELSM01 GRET01 HYLK01 KOMK01 LIMB01 MCGA02 NADS01 PENM01 RUBA01 SLEA01 TAYL02 WALC01 -place_holder
BENL01 BUZI01 COWS01 ERVA01 GRIA01 HYSA01 KOOM01 LIMJ01 MCGA03 ND01 PER*01 RUBH02 SLUS01 TAYN01 WALS01 -place_holder

No_Show Report

Row

data file

No_Show Report

data dimensions

nrow ncol
60 4

Row

column classes/data types

class_type
Date character
Code character
Service character
Staff character

data sample

Date Code Service Staff
04/13/2018 NA SHCW JJ
07/27/2018 AMAB01 SHCM KELLY
06/07/2018 APPS01 SBD KELLY
06/05/2018 ASIS01 SHCW KELLY
05/31/2018 BISD01 CON SINEAD

Row

max column width (in characters)

max_num_char
Date 10
Code 6
Service 5
Staff 6

missing/null values index number

Date Code Service Staff
1

Row

unique Code values

AMAB01 BOYL01 CYRA01 ESKR01 KIRR01 MARK01 OZDC02 RODP01 TAYE01 WEES01
APPS01 CARC01 DHOA01 FIOJ01 KRIJ01 MOOJ01 PHIC01 ROSD02 TURA01 WELN01
ASIS01 CARK01 DODK01 HENI01 KUKK01 NORJ01 PUGS01 ROUT01 TYRA01 WONM02
BISD01 CASH01 DUFD01 HIMJ01 LIMJ01 NOTA01 REDM01 SEEK01 VELA01 NA
BISS01 CULT01 ERWS01 JUNJ01 MANL01 OHLJ01 RIHM01 TAMA01 WAGW01 AMAB01

unique Service values

CBAL CON SHCW
CDPB CTU -place_holder-
CFC FRI -place_holder-
CHLHH SBD -place_holder-
CHLPL SHCM -place_holder-

unique Staff values

BECKY JOANNE
HOUSE KELLY
JJ SINEAD

Product Listing-Retail

Row

data file

Product Listing-Retail

data dimensions

nrow ncol
101 14

Row

column classes/data types

class_type
IsActive logical
Code character
Description character
Supplier character
Brand character
Category character
Price numeric
On Hand numeric
Minimum numeric
Maximum numeric
Cost numeric
COG numeric
YTD numeric
Package logical

data sample

IsActive Code Description Supplier Brand Category Price On Hand Minimum Maximum Cost COG YTD Package
TRUE AGBALANCE335M AG balance 355ml VENUS AG RETAIL 30 2 2 2 0.0 0.0 27.0 FALSE
TRUE AGBOOST355ML AG Boost 355ml VENUS AG RETAIL 30 3 2 2 18.0 54.0 0.0 FALSE
TRUE AGCLOUD107ML AG Cloud 107ml VENUS AG RETAIL 26 4 2 2 15.6 62.4 23.4 FALSE
TRUE AGDRYLIFT44ML AG Dry Lift 44ml VENUS AG RETAIL 24 1 2 2 14.4 14.4 43.2 FALSE
TRUE AGREMEDY148ML AG Remedy 148ml VENUS AG RETAIL 26 3 2 2 15.6 46.8 0.0 FALSE

Row

max column width (in characters)

max_num_char
IsActive 4
Code 14
Description 30
Supplier 6
Brand 6
Category 6
Price 5
On Hand 2
Minimum 1
Maximum 1
Cost 5
COG 6
YTD 6
Package 5

missing/null values index number

IsActive Code Description Supplier Brand Category Price On Hand Minimum Maximum Cost COG YTD Package
35 9 9 10
10 10 35
35 35

Row

unique Supplier values

LOREAL
SHU
TBBS
VENUS
NA

unique Brand values

AG LOREAL NA
DM PURE -place_holder
KM SHU -place_holder

unique Category values

RETAIL
NA

Row

unique Code values

AGBALANCE335M KM BB 350M LP HTU DBLONDE LPWSSCRUFF150M POHCON250ML POSCCON250ML SHU CL MILK150 SHU FIBER150 SHU SHEER 226G SHU VM 2G
AGBOOST355ML KM DO 250ML LP HTU LBROWN MISC POHSCON250ML POSCCON50ML SHU CL SH300M SHU KENGO100M SHU STRAIGH185 SHU WONDER150
AGCLOUD107ML KM ER 100G LP SILVE SH300 NEUAO75M POHSH250ML POSCFL100ML SHU CL TRE200M SHU KW 150ML SHU TEXTURE193 -place_holder-
AGDRYLIFT44ML KM KC 200ML LPHOM ST150M NEUBDL250ML POHSM150ML POSCSH250ML SHU CLAY 75G SHU MASTER75M SHU UM CON250 -place_holder-
AGREMEDY148ML KM ML 150ML LPSB CON250M NEUCLAY50G POHSSH250ML POSCSH50ML SHU CO CON250 SHU MV CON250 SHU UM LEAV100 -place_holder-
AGROSEHIP89ML KM NR 100G LPTAAIRFIX400M POCF200ML POP4PCB100ML POSCSM150ML SHU CO SH400M SHU MV SH300 SHU UM MASK200 -place_holder-
DMPM9.1G KM RRG 100G LPTACON150M POCF30ML POP4PCON250M SHU AMPLE150M SHU COTTON75M SHU SATIN 250M SHU UM SH300 -place_holder-
DMPML9.1G KM SA 200M LPWSBEACHWAVE1 POCVCON250ML POP4PMF145M SHU BB FINE150 SHU EA CM 250M SHU SB CON 250 SHU UR CON250 -place_holder-
ELCHIM DIFFUSE LP HTU AUBURN LPWSCREPAGE200 POCVILM145M POP4PRR200M SHU BB THIC150 SHU EA CREA150 SHU SB MASQ200 SHU UR MAS200 -place_holder-
ELCHIM DRYER LP HTU BLACK LPWSDEPOLISH POCVM238G POP4PSH250ML SHU CL CON250M SHU EA NIGH100 SHU SB SH300 SHU UR SERUM30 -place_holder-
KM AGL 150ML LP HTU BROWN LPWSSAVAGEPANA POCVSH250ML POPUR SH250 SHU CL DRY138G SHU EA OIL 150 SHU SHAPE71G SHU UR SH300 -place_holder-

Row

unique Description values

AG balance 355ml KM Body builder 350m LP hair touch up Dbrown/black LP wild stylers scruff me 150m PO hydrate sh 250ml PO SC superfood masque 150ml Shu color lustre con 250ml Shu Kaze Wave 150ml Shu straightforward 185ml Shu wonder worker 150ml
AG Boost 355ml KM Doo over 250ml LP hair touch up light brown 5 NeuMa Argan Oil 75ml PO hydrate sheer con 250ml PO strength cure con 250ml Shu color lustre drycleaner138 Shu kengo feather 100m Shu texture wave 193g NA
AG Cloud 107ml KM Easy rider 100g LP homme strong 150m NeuMa Blowdry Lotion 250ml PO Hydrate sheer sh 250ml PO strength cure con 50ml Shu color lustre sh 300ml Shu master wax 75m Shu ultimae remedy masque200 -place_holder-
AG Dry Lift 44ml KM Killer curls 200ml LP Silver Shampoo 300ml NeuMa Clay 50g PO Hydrate superfood masque150 PO strength cure sh 250ml Shu Color lustre thermomilk150 Shu muroto volume con250 Shu ultimate remedy con250 -place_holder-
AG Remedy 148ml KM Motion lotion 150ml LP Smart Bond con 250ml PO clean volume con 250ml PO P4P Cool Blonde 100ml PO strength cure sh 50ml Shu color lustre treatment 200 Shu Muroto volume sh300 Shu ultimate remedy serum 30m -place_holder-
AG Rosehip Balm 89ml KM Night Rider 100g LP TA air fix 400ml PO clean volume mousse 238g PO p4p Miracle filler145ml Shu ample angora150m Shu cotton uzu 75m Shu Satin design 250ml shu ultimate remedy sh300 -place_holder-
Design me Puff me 1.9g KM Rough Rider 100g LP TA constructor 150m PO Clean Volume sh 250ml PO p4p Reconstruct Repair 200m Shu BB cream thick 150m Shu essence absolue C.milk 250 Shu shape paste 71g Shu urban moisture con250 -place_holder-
Design me Puff me light 9.1g KM Smooth Again 200ml LP wild stylers beach waves 15 PO color fanatic 200ml PO p4p shampoo 250ml Shu BB serum fine 150m Shu essence absolue cream c150 Shu Sheer lacquer 226g Shu urban moisture leavein100 -place_holder-
Elchim diffuser LP hair touch up Auburn 59ml LP wild stylers crepage de chi PO color fanatic 30ml PO perfect 4 platinum con 250m Shu clay definer 75g Shu essence absolue night 100 Shu silk bloom con 250 Shu urban moisture masque200 -place_holder-
elchim dryer LP hair touch up brown 59ml LP wild stylers depolish 100ml PO CV instant levitation mist PO Purify sh 250ml Shu cleansing oil con 250m Shu essence absolue oil 150m Shu silk bloom masque200 Shu urban moisture sh 300 -place_holder-
KM Anti gravity lotion 150ml LP hair touch up dark blond 59 LP wild stylers savage panache PO hydrate con 250ml PO SC fabulous lengths 95ml Shu cleansing oil sh400ml Shu fiber lift 150ml Shu silk bloom sh300ml Shu Volume Maker 2g -place_holder-

Receipt Transactions

Row

data file

Receipt Transactions

data dimensions

nrow ncol
1934 9

Row

column classes/data types

class_type
Receipt numeric
Date character
Description character
Client character
Staff character
Quantity numeric
Amount numeric
GST numeric
PST numeric

data sample

Receipt Date Description Client Staff Quantity Amount GST PST
10 03/15/2018 Women’s hair cut KERT01 JJ 1 82 4.10 6.56
100 04/05/2018 Women’s hair cut COOM01 SINEAD 1 70 3.50 5.60
1000 06/09/2018 Consultation PEDM01 JJ 1 0 0.00 0.00
1001 06/09/2018 Women’s hair cut BAIS01 KELLY 1 55 2.75 4.40
1003 06/09/2018 Women’s hair cut FRAL01 KELLY 1 65 3.25 5.20

Row

max column width (in characters)

max_num_char
Receipt 4
Date 10
Description 30
Client 7
Staff 6
Quantity 2
Amount 5
GST 6
PST 6

missing/null values index number

Receipt Date Description Client Staff Quantity Amount GST PST
370
371
636

unique Staff values

BECKY JOANNE TANYA
HOUSE KELLY -place_holder-
JJ SINEAD -place_holder-

Column

unique Client values

?A01 BOES01 CHUJ01 DIRK01 GREN01 JARS01 LAMD01 MAPJ01 MUIK01 PIJM01 SAVC01 STEM01 VANJ01
.L01 BONT01 CLAJ01 DODK01 GRET01 JASA01 LANI01 MARA01 MULN01 PIPJ01 SAVP01 STEM02 VARE01
ABL*01 BOOR01 CLAM01 DOLD01 GRIA01 JIAL01 LANR01 MARD01 MUMK01 PIVS01 SCHC01 STEP01 VARL01
ABLD01 BORJ01 CLEE01 DONN01 GRIF01 JM01 LANS01 MARG01 MUNJ01 PLUJ01 SCHH01 STOD01 VAUD01
ADAP01 BOSJ01 CLUS01 DORR01 GRIP01 JOHD01 LAS01 MARK01 MUNM01 PN01 SCHJ01 STOK01 VELA01
AGOE01 BOTC01 COAA01 DOWR01 GROJ01 JOHJ01 LAWJ01 MARM01 MURS01 POIJ01 SCHJ02 STOK02 VERD01
AINM01 BOTK01 COAJ01 DRAA01 GROR01 JOHM01 LEAS01 MARO01 MURS02 POLB01 SCHJ03 STOS01 VERM01
AITS01 BOTN01 COHH01 DRAR01 GUBD01 JONC01 LEB01 MARR01 NADS01 POOD01 SCHP01 STOV01 VERR01
ALCV01 BOTS01 COLB01 DSIC01 GUYD01 JONE01 LEEJ01 MART01 ND01 POTR01 SCOJ01 STRH01 WAGL01
ALIA01 BOWM01 COLC01 DUBO01 HACL01 JONK01 LEEK01 MASA01 NEEL01 POUG01 SCOL01 STUM01 WAGM01
ALIR01 BOWV01 COLE01 DUFD01 HADD01 JONW01 LEEM01 MASR01 NELT01 PRIL01 SEDD01 SUNS01 WAIS01
ALIR02 BOYL01 COLG01 DUFM01 HAMA01 JOYA01 LEEN01 MAUA01 NEML01 PRIM01 SENM01 SUPR01 WAL*01
ALLM01 BRAN01 COLJ01 ELSM01 HAMD01 JUDJ01 LEEO01 MAXI01 NEMM01 PUGS01 SHAA01 SWAM01 WALC01
ALVJ01 BREF01 COLJ02 ERVA01 HAMN01 JURS01 LEEP01 MCAJ01 NGC01 PURD01 SHAG01 TAGS02 WALK_IN
ALZR01 BREH01 COLR01 ERWS01 HARW01 KALA01 LEES01 MCCB01 NGUJ01 RABH01 SHAI01 TAMA01 WALS01
AMAK01 BREM01 COLR02 ESKR01 HASS01 KASJ01 LEEV01 MCCK01 NGUS01 RADP01 SHAM01 TANJ01 WALT01
ANDE01 BRES01 COMJ01 EZIR01 HEIM01 KATM01 LEFN01 MCGA01 NICL01 RANZ01 SHAP01 TASJ01 WANM01
ANDJ01 BRIK01 CONJ01 FAIL01 HELJ01 KATS01 LEHF01 MCGA02 NICM01 REDM01 SHAS01 TASR01 WARG01
ANDM01 BROA01 CONK01 FANM01 HENA01 KEAJ01 LEHJ01 MCGA03 NICP01 REIC01 SHEK01 TAYB01 WARK01
ANDN01 BROF01 COOM01 FAUA01 HENB01 KEIS01 LEHN01 MCGD01 NICS01 REIL01 SHIG01 TAYD01 WARN01
APTM01 BROS01 CORS01 FEDS01 HENI01 KELA01 LEIF01 MCGM01 NIDN01 REIM01 SHIL01 TAYE01 WASS01
ARAI01 BROS02 CORS02 FELH01 HERD01 KELR01 LEIG01 MCGV01 NIES02 RETM01 SIDA01 TAYJ01 WAX*01
ARBA01 BROT01 COWL01 FER*01 HESM01 KEN*01 LEL01 MCKS01 NIKK01 RICC01 SILG01 TAYJ02 WEEJ01
ARMG01 BUDG02 COWS01 FERK01 HIGA01 KENH01 LELS01 MCKT01 NIZS01 RICP01 SINJ01 TAYL01 WEES01
ARRM01 BUEM01 COXH01 FIGM01 HILD01 KENJ01 LEM01 MCLE01 NORB01 RICS01 SINM01 TAYL02 WEIA01
ATKN01 BUFJ01 CRAY01 FIND01 HILE01 KERT01 LEVL01 MCLI01 NORE01 RIDY01 SINS01 TAYN01 WEIS01
ATLB01 BUFW01 CRIV01 FINW01 HILJ01 KHAN01 LEWH01 MCMC01 NORJ01 RIHM01 SINS02 TAYR01 WELL01
AVPR01 BURD01 CROB01 FISV01 HIMB01 KHUN01 LEWH02 MCND01 NORL01 RIJR01 SINT01 TAYT01 WELN01
AYKS01 BURK01 CULT01 FLAC01 HOAB01 KIMF01 LIMB01 MCNR01 NOWT01 RIVA01 SINW01 TERJ01 WHIA01
BAIL01 BURL01 CURK01 FLAD01 HOAS01 KIMH01 LIND01 MCPL01 OBRK01 RIZB01 SIRM01 THIA01 WICJ01
BAIS01 BURT01 CYRA01 FLAS01 HOC01 KIMS01 LINJ01 MCWC01 ODOA01 ROBM01 SITB01 THOA01 WIL*01
BALJ01 BUSC01 CZES01 FLEB01 HOFA01 KIMY01 LINM01 MELE01 OGDJ01 ROBT01 SKUD01 THOD01 WILB01
BALO01 BUSM01 DACJ01 FLEC01 HOFJ01 KINN01 LINM02 MER*01 OGOE01 RODP01 SKUT01 THOJ01 WILK01
BANR01 BUZI01 DAJR01 FLEL01 HOG*01 KIRJ01 LOCS01 MERM01 OGRS01 ROGE01 SLEA01 THOW01 WILS01
BARA01 BYRP01 DALR01 FOXS01 HOLD01 KLEA01 LOSC01 MERN01 OHA*01 ROLD01 SLUS01 THOW02 WILT01
BARE01 CABL01 DAMK01 FRAL01 HOLG01 KM01 LUCL01 MESK01 OHAG01 RONM01 SMAN01 TIEM01 WINJ01
BARJ01 CALC01 DANG01 FRAM01 HOLL01 KOEE01 LUL01 MESN01 OKEM01 ROPR01 SMIC01 TIML01 WINL01
BARW01 CALD01 DANJ01 FRAW01 HOLS01 KOLK01 LURM01 METJ01 OSES01 ROSD01 SMIL01 TINA01 WITC01
BATP01 CAND01 DANJ02 FREJ01 HOP01 KOMK01 LYK01 MICK01 OWED01 ROSL01 SMIS01 TINA02 WONM02
BEAA01 CARB01 DANJ03 FREK01 HOS01 KOOM01 LYL01 MICM01 OZIS01 ROSM01 SOLR01 TINT01 WOOD01
BEAA02 CARC01 DAS*01 FZUA01 HOT01 KOOM02 LYNG01 MILE01 PAE01 ROST01 SORM01 TINT02 WOOD02
BEAL01 CARK01 DAV*01 GA01 HOUC01 KOSA01 LYOH01 MILJ01 PAIK01 ROWD01 SPEC01 TOBJ01 WOOJ01
BEBY01 CASH01 DAVA01 GAGC01 HOUN01 KOSI01 MACA01 MINA01 PANB01 ROZM01 SPEK01 TOHS01 WOOJ02
BELK01 CASN01 DAWP01 GALB01 HOWC01 KOZC01 MACB01 MINA02 PANP01 RUBA01 SPIJ01 TOLM01 WRI*01
BENC01 CAYK01 DD01 GALG01 HUAR01 KRAS01 MACD01 MOGS01 PARC01 RUBH02 SPRK01 TOMA01 WRIM01
BENL01 CECA01 DEAM01 GARJ01 HUDM01 KRAV01 MACK01 MOHM01 PARD01 RUDJ01 SQUN01 TOMV01 WYRC01
BENS01 CHA*01 DEH01 GARJ02 HUDS01 KREJ01 MACM01 MOHR01 PARJ01 RUGN01 STAD01 TOPS01 YAGL01
BENS02 CHAE01 DEK01 GELK01 HUTP01 KRIA01 MACR01 MOIB01 PARJ02 RUMJ02 STAT01 TRAJ01 YANC01
BERH01 CHAG01 DELC01 GHOP01 HUYK01 KRIJ01 MACS01 MOLA01 PARS01 RUPN01 STEA01 TRAS01 YANJ01
BERI01 CHEW01 DENB01 GIAL01 HYLK01 KUCM02 MACT01 MOLC01 PATB01 RUTA01 STEB01 TRAS02 YANM01
BESJ01 CHIC01 DES01 GIEC01 HYSA01 KUKH01 MACT02 MONE01 PAUB01 RUTM01 STEC01 TRES01 YATT01
BEZK01 CHIT01 DESA01 GOOA02 IADA01 KUKK01 MACV01 MONL01 PEDM01 RUTR01 STED01 TRIL01 YEHC01
BHAJ01 CHMY01 DEUD01 GORM01 INFB01 KULR01 MAEJ01 MOOE01 PEDM02 SAMB01 STEH01 TRUA01 YOUS01
BHAT01 CHOK01 DEYS01 GOSD01 INGS01 KUMA01 MAF*01 MOOH01 PEEL01 SAMJ01 STEI01 TRUA02 YUES01
BINP01 CHOT01 DHAL01 GOUE01 IORC01 KURG01 MAIK01 MOOJ01 PENM01 SARA01 STEJ01 TSAR01 ZHAV01
BISS01 CHRJ01 DHAN01 GRAA01 IRWE01 KUZD01 MAKM01 MORB01 PER*01 SARK01 STEK01 TSUL01 ZHIJ01
BIZK01 CHUA01 DHAS01 GREB01 JACA01 KWOR01 MAKP01 MORD01 PERB01 SARM01 STEK02 TUMM01 ZIMJ01
BLAJ01 CHUC01 DHAS02 GREL01 JAMM01 LAIA01 MALJ01 MOSR01 PHIC01 SASA01 STEL01 TYRK01 ZUGL01
BLAR01 CHUH01 DINJ01 GREM01 JARM01 LAMC01 MANL01 MOYJ01 PHIJ01 SASM01 STEL02 VALT01 ZUKS01

Service Listing

Row

data file

Service Listing

data dimensions

nrow ncol
33 6

Row

column classes/data types

class_type
IsActive logical
Code character
Desc character
Cate character
Price numeric
Cost numeric

data sample

IsActive Code Desc Cate Price Cost
TRUE CAL accent lights COLOR 0 10
TRUE CALC Accent lights and color COLOR 0 10
TRUE CAS Color additional service COLOR 0 10
TRUE CBAL Balayage COLOR 0 10
TRUE CCAMO Men’s camo color COLOR 45 5

Row

max column width (in characters)

max_num_char
IsActive 4
Code 9
Desc 30
Cate 5
Price 3
Cost 2

missing/null values index number

IsActive Code Desc Cate Price Cost
20

unique Category values

COLOR
MISC
STYLE

Row

unique Code values

CAL CBAL CDPB CHLFHC CHLPHC CON EXT MISC SBD5+1 SHCM SMO
CALC CCAMO CFC CHLHH CHLPL CT F&F NECK SDUD SHCW SSUD
CAS CCO CHLFH CHLHHC CMT CTU FRI SBD SHCC SMARTBOND TRE 1

Row

unique Description values

accent lights Blow dry bundle 5+1 Color additional service Color mini touch up Consultation Fringe cut Highlights full Highlights partial miscellaneous Shampoo updo Treatments 1
Accent lights and color Blowdry Color correction Color toner double process blonde Full Highlights and color Highlights half head Men’s camo color neck trim Smart Bond Women’s hair cut
Balayage Children (under 12) Color full color Color touch up Dry updo Hair extensions Highlights half head and color Men’s hair cut Partial highlights and color Smoothing treatment NA

Starting Data Tables/Files

Raw Data Table Names and Columns

ClientCancellations FutureBookings_AllClients No_ShowReport ProductListing_Retail ReceiptTransactions ServiceListing
Cancel Date Code Date IsActive Receipt IsActive
Code Staff Code Code Date Code
Service Service Service Description Description Desc
Staff Date Staff Supplier Client Cate
Booking Date Time — Brand Staff Price
Canceled By TimeInt — Category Quantity Cost
Days — — Price Amount —
— — — On Hand GST —
— — — Minimum PST —
— — — Maximum — —
— — — Cost — —
— — — COG — —
— — — YTD — —
— — — Package — —

DB Structure After Redesign

Row

Data Dictionary Notes

Notes/observations about the data

  • Column headings were renamed in MySQL tables for clarity.

  • While reviewing table data during database construction, certain column calculations were identified. The select calculations identified are as follows:


SQL Table Column Notes/Meaning
product_listing COG calculated as \(COG = num\_on\_hand * cost\); COG definition/meaning is unknown
retail_transactions GST calculated as \(GST = Amount * 0.05\); GST assumed to mean “Govt. Sales Tax”
retail_transactions PST calculated as \(PST = Amount * 0.08\); PST assumed to mean “Provincial Sales Tax”

Sales/Revenue

Row

Date Range (Receipt Transactions)

begin_date end_date
2018-03-15 2018-07-29

Total Sales

115809.03

Total Sales (Services)

108669.7

Total Sales (Products)

7066.61

Total Sales (MISC - Service and/or Product)

3

Row

# Services Offered (Active)

33

# Products Offered (Active)

101

Row

Products Out-of-Stock (Active)

prod_category prod_code prod_desc prod_brand prod_supplier num_on_hand
RETAIL KM BB 350M KM Body builder 350m KM TBBS 0
RETAIL KM ML 150ML KM Motion lotion 150ml KM TBBS 0
RETAIL KM NR 100G KM Night Rider 100g KM TBBS 0
RETAIL KM RRG 100G KM Rough Rider 100g KM TBBS 0
RETAIL KM SA 200M KM Smooth Again 200ml KM TBBS 0
RETAIL NEUAO75M NeuMa Argan Oil 75ml KM TBBS 0
RETAIL NEUBDL250ML NeuMa Blowdry Lotion 250ml KM TBBS 0
RETAIL SHU KENGO100M Shu kengo feather 100m SHU SHU 0

Row

Top 10 Services

service_code service_category service_desc total_service_sales %_service_sales %_total_sales
SHCW STYLE Women’s hair cut 50425.7 46.40 43.54
SHCM STYLE Men’s hair cut 16725.0 15.39 14.44
SBD STYLE Blowdry 11879.0 10.93 10.26
CTU COLOR Color touch up 5230.0 4.81 4.52
CHLPL COLOR Highlights partial 5075.0 4.67 4.38
SBD5+1 STYLE Blow dry bundle 5+1 4645.0 4.27 4.01
CALC COLOR Accent lights and color 3415.0 3.14 2.95
CFC COLOR Color full color 3160.0 2.91 2.73
CBAL COLOR Balayage 2170.0 2.00 1.87
CHLPHC COLOR Partial highlights and color 1860.0 1.71 1.61

Top 10 Products

prod_code prod_category prod_desc total_product_sales %_product_sales %_total_sales
ELCHIM DRYER elchim dryer 1269.90 17.97 1.10
SHU CO SH400M RETAIL Shu cleansing oil sh400ml 501.20 7.09 0.43
SHU EA OIL 150 RETAIL Shu essence absolue oil 150m 476.10 6.74 0.41
SHU EA CM 250M RETAIL Shu essence absolue C.milk 250 302.40 4.28 0.26
SHU CO CON250 RETAIL Shu cleansing oil con 250m 246.40 3.49 0.21
KM NR 100G RETAIL KM Night Rider 100g 208.00 2.94 0.18
KM ML 150ML RETAIL KM Motion lotion 150ml 201.60 2.85 0.17
POCF200ML RETAIL PO color fanatic 200ml 167.75 2.37 0.14
POHSH250ML RETAIL PO hydrate sh 250ml 155.36 2.20 0.13
POSCSH250ML RETAIL PO strength cure sh 250ml 155.36 2.20 0.13

Row

Bottom 5 Services

service_code service_category service_desc total_service_sales %_service_sales %_total_sales
MISC MISC miscellaneous -2458.00 -2.26 -2.12
CON MISC Consultation -894.00 -0.82 -0.77
CAS COLOR Color additional service 55.00 0.05 0.05
F&F MISC 72.72 0.07 0.06
CHLFHC COLOR Full Highlights and color 160.00 0.15 0.14

Bottom 5 Products

prod_code prod_category prod_desc total_product_sales %_product_sales %_total_sales
POSCSH50ML RETAIL PO strength cure sh 50ml 9.25 0.13 0.01
LPWSDEPOLISH RETAIL LP wild stylers depolish 100ml 14.70 0.21 0.01
KM RRG 100G RETAIL KM Rough Rider 100g 16.00 0.23 0.01
DMPML9.1G RETAIL Design me Puff me light 9.1g 17.55 0.25 0.02
POCF30ML RETAIL PO color fanatic 30ml 17.57 0.25 0.02

Row

Daily Sales

Row

Sales by Week

Row

Daily Sales by Day-of-the-Week (DOW)

Row

Total Sales Aggregated by (DOW)

Clients

Row

Date Range - Receipts

begin_date end_date
2018-03-15 2018-07-29

Date Range - Cancellations

begin_cancel_date end_cancel_date begin_booking_date end_booking_date
2018-03-27 2018-07-28 2018-03-29 2018-09-21

Row

Date Range - No-Show Report

begin_booking_date end_booking_date
2018-03-14 2018-07-29

Date Range - Future Bookings

begin_booking_date end_booking_date begin_booking_time end_booking_time
2018-03-14 2019-02-15 08:00:00 19:30:00

Row

Total Clients (in records)

822

Total Walk-ins (on receipts)

29

Row

Clients (on records, on receipts, excludes walk-ins)

766

Repeat Clients (> 1 visit, on receipts, excludes walk-ins)

315

One-time Clients (on-receipts, excludes walk-ins)

451

Row

10 Most Frequent Clients (excludes walk-ins) (scrollable)

Row

Clients w/ Most Cancellations

Staff

Row

Date Range - Receipts

begin_date end_date
2018-03-15 2018-07-29

Row

Earnings by Staff

Row

Appointments by Staff (excludes Walk-Ins)

Row

Cancellations by Staff

Row

No-Shows by Staff

Walk-Ins Report

Walk-Ins Report (scrollable)

Cancellations Report

Cancellations Report (scrollable)

No-Show Report

No-Show Report (scrollable)

Price Differences Report

Price Differences Report (scrollable)